﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using HZ.MBSM.DBUtility;
using System.Text.RegularExpressions;
public partial class Agency_Tra_InsertNopass : System.Web.UI.Page
{
    private string fp, fp_EXT, sheetName;

    private string Goodsid = "药品流水号";
    private int int_Goodsid = -1;   //药品流水号的列索引

    private string companyname_ps = "主配送企业字典编码";
    private int int_companyname_ps = -1; //配送企业编码的列索引

    private string companyname_ps_b = "二级主配送企业字典编码";
    private int int_companyname_ps_b = -1;    //交易价的列索引

    private string areaname = "地区";
    private int int_areaname = -1;    //采购数量的列索引

    private string dwname;
    private long accountid;


    private Regex pslReg = new Regex("^[0-9]*[1-9][0-9]*$");  //正整数判断
    private Regex jeReg = new Regex("^(\\d+)(\\.\\d{1,2})?$");  //金额判断
    private Regex dateReg = new Regex(@"^((((1[6-9]|[2-9]\d)\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$");



    protected void Page_Load(object sender, EventArgs e)
    {
        System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
        _AccountType.Add(HZ.MBSM.DAL.AccountType.enumAttorney);
        if (HZ.MBSM.BLL.Login.CheckAssert(_AccountType))
        {
            HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
            if (_Account != null)
            {
                dwname = _Account.DWName;
                accountid = _Account.AccountId;

            }
        }
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        //DataTable dtSource= getSourceData();
        ////GridView1.AllowPaging = true;
        ////GridView1.PageSize = 20;
        //this.GvSource.DataSource = dtSource;
        //this.GvSource.DataBind();
    }


    /// <summary>
    /// 获取数据源,绑定数据
    /// </summary>
    /// <returns></returns>
    private void BindSourceData(string FilePathAndName, string childSheet)
    {
        DataTable dt = new DataTable();
        try
        {
            using (System.Data.OleDb.OleDbConnection dbConnExl = getConnection(FilePathAndName))
            {
                dbConnExl.Open();
                string sConnectSql = ConfigurationManager.ConnectionStrings[0].ToString(); ;
                SqlConnection dbConnSql = new SqlConnection(sConnectSql);
                string sqlExl = "SELECT * FROM [" + childSheet + "]";
                System.Data.OleDb.OleDbDataAdapter objAdapExl = new System.Data.OleDb.OleDbDataAdapter(sqlExl, dbConnExl);
                DataSet objDataSet = new DataSet();
                objAdapExl.Fill(objDataSet);
                dt = objDataSet.Tables[0];
                dbConnExl.Close();
                GvSource.DataSource = dt;
                GvSource.DataBind();
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }



    /// <summary>
    /// 获取数据源,生成datatable对象
    /// </summary>
    /// <returns></returns>
    private DataTable GetSourceData(string FilePathAndName, string childSheet)
    {
        DataTable dt = new DataTable();
        try
        {
            using (System.Data.OleDb.OleDbConnection dbConnExl = getConnection(FilePathAndName))
            {
                dbConnExl.Open();
                string sConnectSql = ConfigurationManager.ConnectionStrings[0].ToString(); ;
                SqlConnection dbConnSql = new SqlConnection(sConnectSql);
                string sqlExl = "SELECT * FROM [" + childSheet + "]";
                System.Data.OleDb.OleDbDataAdapter objAdapExl = new System.Data.OleDb.OleDbDataAdapter(sqlExl, dbConnExl);
                DataSet objDataSet = new DataSet();
                objAdapExl.Fill(objDataSet);
                dt = objDataSet.Tables[0];
                dbConnExl.Close();
                return dt;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
        return dt;
    }

    /// <summary>
    /// 获取源文件的子表
    /// </summary>
    /// <returns></returns>
    private void BindSourceSheet(string FilePathAndName)
    {
        try
        {
            using (System.Data.OleDb.OleDbConnection MyConn = getConnection(FilePathAndName))
            {
                MyConn.Open();
                DataTable schemaTable = MyConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DDLsheet.Items.Clear();
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string TableName = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
                    if (TableName.Substring(TableName.Length - 1, 1) != "_")
                    {
                        this.DDLsheet.Items.Add(TableName);
                    }
                }
                DDLsheet.Items.Insert(0, "");
                DDLsheet.SelectedIndex = 0;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }


    /// <summary>
    /// 获取连接对象
    /// </summary>
    /// <param name="FilePathAndName"></param>
    /// <returns></returns>
    private System.Data.OleDb.OleDbConnection getConnection(string FilePathAndName)
    {
        String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePathAndName + ";Extended Properties=Excel 8.0;";
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
        return Conn;
    }


    /// <summary>
    /// 下一步
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_NextButtonClick(object sender, WizardNavigationEventArgs e)
    {
        labmess0.Visible = false;
        labmess1.Visible = false;
        labmess2.Visible = false;
        labmess3.Visible = false;



        string mess = "";
        try
        {
            switch (e.CurrentStepIndex)
            {
                case 0:  //打开文件,判断是否是excel文件
                    //if (ViewState["fp"] != null)
                    //{
                    //    this.filepath.Attributes.Add("value", ViewState["fp"].ToString());
                    //}
                    mess = CheckSourceType();
                    if (mess != "")
                    {
                        labmess0.Visible = true;
                        labmess0.Text = mess;
                        e.Cancel = true;
                    }
                    else
                    {
                        //上传文件
                        DateTime dt = new DateTime();
                        dt = DateTime.Now;
                        Random rnd = new Random(dt.Millisecond);

                        delSourceExcel();   //删除之前上传的excel文件

                        fp = Server.MapPath("../UploadFile_SupplyPlan/") + accountid + "plan-" + rnd.Next(10000) + ".xls";
                        filepath.SaveAs(fp);

                        ViewState["fp"] = fp;
                        BindSourceSheet(ViewState["fp"].ToString());
                    }
                    break;
                case 1:  //选择子表,判断用户是否选择了子表
                    mess = CheckChoiceSheet();
                    if (mess != "")
                    {
                        labmess1.Visible = true;
                        labmess1.Text = mess;
                        e.Cancel = true;
                    }
                    else
                    {
                        ViewState["sheetName"] = sheetName;
                        BindSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());
                    }
                    break;
                case 2:  //打开子表,判断文件是否符合要求
                    BindSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());
                    mess = CheckSourceColumns();
                    if (mess != "")
                    {
                        labmess2.Visible = true;
                        labmess2.Text = mess;
                        e.Cancel = true;
                    }
                    else
                    {
                        //txtPlanName.Text = dwname + System.DateTime.Now.ToShortDateString() + "采购单";
                        //  txtRemarks.Text = "接口管理导入";
                    }

                    break;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }


    /// <summary>
    /// 点击导航栏按钮
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_SideBarButtonClick(object sender, WizardNavigationEventArgs e)
    {

        Wizard1_NextButtonClick(sender, e);
    }

    /// <summary>
    /// 开始导入,判断采购单名称和时间是否有效
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
    {

        startImport();

    }

    /// <summary>
    /// 检查源文件的格式,是否是excel文件
    /// </summary>
    /// <returns></returns>
    private string CheckSourceType()
    {
        string mess = "";
        fp = this.filepath.PostedFile.FileName;
        if (fp == "")
        {
            mess = "对不起,请您选择要打开的采购单!";
            return mess;
        }
        int ipost = fp.LastIndexOf(".");
        if (ipost == -1)
        {
            fp_EXT = "";
        }
        else
        {
            fp_EXT = fp.Substring(ipost, fp.Length - ipost);
        }
        if (fp_EXT != ".xls")
        {
            mess = "对不起,您只能打开Excel格式的采购单!";
        }
        return mess;
    }


    /// <summary>
    /// 检查用户是否选择了excel文件的子表
    /// </summary>
    /// <returns></returns>
    private string CheckChoiceSheet()
    {
        string mess = "";
        if (DDLsheet.Items.Count == 0)
        {
            mess = "对不起,请您选择要打开的采购单!";
        }
        sheetName = DDLsheet.Items[DDLsheet.SelectedIndex].Text.Trim();

        if (sheetName == "")
        {
            mess = "对不起,请选择子表!";
        }
        return mess;
    }


    /// <summary>
    /// 检查采购单源文件是否符合规定(固定格式)
    /// </summary>
    /// <returns></returns>
    private string CheckSourceColumns()
    {
        string mess = "";
        DataTable dt = (DataTable)GvSource.DataSource;
        Goodsid = "流水号";
        int_Goodsid = -1;   //药品流水号的列索引

        companyname_ps = "主配送企业";
        int_companyname_ps = -1; //配送企业编码的列索引

        companyname_ps_b = "二级配送企业";
        int_companyname_ps_b = -1;    //二级配送企业的列索引

        areaname = "配送地区";
        int_areaname = -1;    //配送地区的列索引

        int colNum = dt.Columns.Count;
        if (colNum == 0)
        {
            mess = "对不起,您选择的文件是空的!";
            return mess;
        }

        for (int i = 0; i < colNum; i++)
        {
            if (dt.Columns[i].ColumnName.Trim() == Goodsid)
            {
                if (int_Goodsid == -1)
                {
                    int_Goodsid = i;
                    continue;
                }
                else
                {
                    mess = "对不起,您选择的文件中表头[流水号]不唯一!";
                    return mess;
                }
            }

            if (dt.Columns[i].ColumnName.Trim() == companyname_ps)
            {
                if (int_companyname_ps == -1)
                {
                    int_companyname_ps = i;
                    continue;
                }
                else
                {
                    mess = "对不起,您选择的文件中表头[主配送企业]不唯一!";
                    return mess;
                }
            }

            if (dt.Columns[i].ColumnName.Trim() == companyname_ps_b)
            {
                if (int_companyname_ps_b == -1)
                {
                    int_companyname_ps_b = i;
                    continue;
                }
                else
                {
                    mess = "对不起,您选择的文件中表头[二级配送企业]不唯一!";
                    return mess;
                }
            }

            if (dt.Columns[i].ColumnName.Trim() == areaname)
            {
                if (int_areaname == -1)
                {
                    int_areaname = i;
                    continue;
                }
                else
                {
                    mess = "对不起,您选择的文件中表头[配送地区]不唯一!";
                    return mess;
                }
            }
        }


        if (int_Goodsid == -1)
        {
            mess = "对不起,您选择的文件中,表头[流水号]不存在!";
            return mess;
        }

        if (int_companyname_ps == -1)
        {
            mess = "对不起,您选择的文件中,表头[主配送企业]不存在!";
            return mess;

        }

        if (int_companyname_ps_b == -1)
        {
            mess = "对不起,您选择的文件中,表头[二级配送企业]不存在!";
            return mess;

        }

        if (int_areaname == -1)
        {
            mess = "对不起,您选择的文件中,表头[配送地区]不存在!";
            return mess;

        }


        ViewState["int_Goodsid"] = int_Goodsid;
        ViewState["int_companyname_ps"] = int_companyname_ps;
        ViewState["int_companyname_ps_b"] = int_companyname_ps_b;
        ViewState["int_areaname"] = int_areaname;

        return mess;
    }



    /// <summary>
    /// 删除上传的excel文件
    /// </summary>
    private void delSourceExcel()
    {
        if (ViewState["fp"] == null) { return; }
        if (System.IO.File.Exists(ViewState["fp"].ToString()))
        {
            System.IO.File.Delete(ViewState["fp"].ToString());
        }
    }

    /// <summary>
    /// 开始导入
    /// </summary>
    private void startImport()
    {
        string planname = "";// txtPlanName.Text.Trim();
        int planid = -1;
        int succROW = 0;                      //导入成功记录数
        try
        {
            DataTable dtSource = GetSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());

            delSourceExcel();

            int totalROW = dtSource.Rows.Count;   //源文件总记录数

            //保存采购单明细
            int int_Goodsid = int.Parse(ViewState["int_Goodsid"].ToString());
            int int_companyname_ps = int.Parse(ViewState["int_companyname_ps"].ToString());
            int int_companyname_ps_b = int.Parse(ViewState["int_companyname_ps_b"].ToString());
            int int_areaname = int.Parse(ViewState["int_areaname"].ToString());
            Db db;
            string sql = "";
            foreach (DataRow dr in dtSource.Rows)
            {
                string str_Goodsid = dr[int_Goodsid].ToString().Trim();     //药品流水号  正整数
                string str_companyname_ps = dr[int_companyname_ps].ToString().Trim(); // 
                string str_companyname_ps_b = dr[int_companyname_ps_b].ToString().Trim();       // 
                string str_areaname = dr[int_areaname].ToString().Trim();       // 
                string str_SupplyGoodsId = "";

                SqlParameter[] para;
                if (pslReg.IsMatch(str_Goodsid))
                {
                    sql = "select SupplyPlan_GoodsNext.SupplyGoodsId  as cum,Accountid_ps_M,Accountid_ps_B1 from SupplyPlan_Goods,SupplyPlan_GoodsNext where SupplyPlan_Goods.SupplyGoodsId=SupplyPlan_GoodsNext.SupplyGoodsId and isuse=1 and goodsid=" + str_Goodsid + " and areaname='" + str_areaname + "' and companyname_ps_M='" + str_companyname_ps + "' and companyname_ps_B1='" + str_companyname_ps_b + "'";
                    db = new Db();
                    System.Data.IDataReader drr = db.DataReader(sql);
                    bool isImport = true;
                    if (drr.Read())   //导入的数据属于医院的采购目录内药品
                    {
                        if (drr["cum"].ToString() == "")   //无此记录
                        {
                            isImport = false;
                        }
                        else
                        {
                            str_SupplyGoodsId = drr["cum"].ToString();
                            str_companyname_ps = drr["Accountid_ps_M"].ToString();
                            str_companyname_ps_b = drr["Accountid_ps_B1"].ToString();
                        }
                    }
                    else
                    {
                        isImport = false;
                    }

                    drr.Close();
                    db.Close();

                    if (isImport)  //开始导入药品到采购单明细表buyplangoods
                    {
                        //create proc SP_insert_Buyplangoods
                        //(
                        //    @supplygoodsid	bigint,	--药品流水号
                        //    @BuyPlanId	bigint,	--采购单编码
                        //    @accountid_yy	bigint,	--医院编码
                        //    @accountid_ps	bigint,	--配送企业编码
                        //    @goodsnum	int,	--采购数量
                        //    @tradecompanyname_ps_b	float,	--交易价
                        //    @success	int=0	output
                        //)
                        para = new SqlParameter[6];
                        para[0] = new SqlParameter("@Goodsid", str_Goodsid);                
                        para[1] = new SqlParameter("@areaname", str_areaname);
                        para[2] = new SqlParameter("@SupplyGoodsId", str_SupplyGoodsId);
                        para[3] = new SqlParameter("@companyname_ps", str_companyname_ps);
                        para[4] = new SqlParameter("@companyname_ps_b", str_companyname_ps_b);
                        para[5] = new SqlParameter("@success", SqlDbType.Int);
                        para[5].Direction = ParameterDirection.Output;
                        db = new Db();
                        int succ = db.ExecuteProcedure("P_updateSupplyPlan_GoodsNext", 5, para);
                        db.Close();
                        if (succ == 1)  //导入成功,删除此药品
                        {
                            succROW++;
                            dr.Delete();
                        }
                    }
                }
            }

            labmess4.Visible = true;
            if (succROW == totalROW)
            {
                this.labmess4.Text = "更新,已经全部成功!,总计更新 " + succROW + " 条记录！";

            }
            else
            {
                if (succROW != 0)
                {
                    this.labmess4.Text = "文件中有 " + succROW + " 条更新成功, " + (totalROW - succROW) + " 条导入失败(列入下表)";
                }
                else
                {
                    this.labmess4.Text = "文件导入失败!下表列出未能更新的药品信息.";
                }
                this.GvNoImport.DataSource = dtSource;
                GvNoImport.DataBind();
            }

        }
        catch (Exception ex)
        {

            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }




    /// <summary>
    /// 替换错误提示中的特殊字符
    /// </summary>
    /// <param name="mess"></param>
    /// <returns></returns>
    private string EncodeHtml(string mess)
    {
        string[] special = new string[] { "\"", "'", "\n", "\\", "\r\n" };
        foreach (string s in special)
        {
            mess = mess.Replace(s, "");
        }
        return mess;
    }
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        downLoadUnImport(GvNoImport);
    }

    private void downLoadUnImport(GridView gv)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
        Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("未更新的明细.xls"));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        gv.RenderControl(oHtmlTextWriter);

        // 把文件流发送到客户端 
        Response.Write(oStringWriter.ToString());
        // 停止页面的执行
        Response.End();
    }


    public override void VerifyRenderingInServerForm(Control control)
    {
        //OverRide　为了使导出成Excel可行！
    }
}
